package com.sh.data.engine.domain.shims.db;

import com.sh.data.engine.domain.base.model.enums.Database;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableSizeDomain;
import com.sh.data.engine.domain.shims.sql.builder.DDLBuilder;
import com.sh.data.engine.domain.shims.sql.builder.QueryBuilder;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.*;

/**
 * 通用RDBMS数据库管理器
 */
@Slf4j
public abstract class GenericDbManager extends BaseDbManager {

    public GenericDbManager(DbOptions options) {
        super(options);
    }

    @Override
    public Connection getConnection() throws SQLException {
        return makeConnection();
    }

    @Override
    public List<TableInfoDomain> getFliterTableList(String dbName, String schema, String tableSQL)
        throws SQLException {

        return null;
    }

    @Override
    public List<FieldInfoDomain> getFliterFieldList(String tableName, String fieldName)
        throws SQLException {
        return null;
    }

    @SneakyThrows
    @Override
    public List<String> listFieldData(String sql, Connection connection) {
        List<String> res = new ArrayList<>();
        try (PreparedStatement pstmt = createPreparedStatement(connection, sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Object object = rs.getObject(1);
                res.add(object.toString());
            }
        } catch (SQLException e) {
            log.error("异常{}", e.getMessage());
        }
        return res;
    }

    @Override
    public void testConnection() throws SQLException {
        Integer timeout = dbOptions.getLoginTimeout();
        if (timeout == null) {
            timeout = 10;
        }
        int loginTimeout = DriverManager.getLoginTimeout();
        // 再设置成默认值，以防对其他地方有影响
        DriverManager.setLoginTimeout(timeout);
        try (Connection conn = getConnection()) {
        } catch (SQLException e) {
            throw new SQLException(toReadableMsg(e), e);
        } finally {
            DriverManager.setLoginTimeout(loginTimeout);
        }
    }

    protected Connection makeConnection() throws SQLException {

        Connection connection;
        String username = dbOptions.getUsername();
        String password = dbOptions.getPassword();
        String url = dbOptions.getUrl();
        Properties connectionParams = dbOptions.getConnectionParams();

        int oldTimeOut = DriverManager.getLoginTimeout();
        final Integer loginTimeout = dbOptions.getLoginTimeout();

        try {
            if (null != loginTimeout) {
                DriverManager.setLoginTimeout(loginTimeout);
            }

            if (connectionParams != null && connectionParams.size() > 0) {
                log.debug(
                    "User specified connection params. "
                        + "Using properties specific API for making connection.");

                Properties props = new Properties();
                if (username != null) {
                    props.put("user", username);
                }

                if (password != null) {
                    props.put("password", password);
                }

                props.putAll(connectionParams);
                connection = DriverManager.getConnection(url, props);
            } else {
                log.debug(
                    "No connection paramenters specified. " + "Using regular API for making connection.");
                if (username == null) {
                    connection = DriverManager.getConnection(url);
                } else {
                    connection = DriverManager.getConnection(url, username, password);
                }
            }
        } finally {
            if (null != loginTimeout) {
                DriverManager.setLoginTimeout(oldTimeOut);
            }
        }

        return connection;
    }

    @Override
    public String getDbVersion() throws SQLException {
        String queryVersionSql = getQueryVersionSql();

        final List<String> stringList = queryForString(queryVersionSql);

        return stringList.isEmpty() ? null : stringList.get(0);
    }

    protected List<String> queryForString(String sql) throws SQLException {

        if (StringUtils.isBlank(sql)) {
            return null;
        }

        List<String> stringList = new ArrayList<>();

        try (final Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet.next()) {
                stringList.add(resultSet.getString(1));
            }
        }
        return stringList;
    }

    protected boolean doAlertFiledDesc(String sql) throws SQLException {
        if (StringUtils.isBlank(sql)) {
            return false;
        }

        final Connection connection = getConnection();
        PreparedStatement pstmt = connection.prepareStatement(sql);
        return pstmt.execute();
    }

    /**
     * 获取查询版本的sql语句
     *
     * @return
     */
    public abstract String getQueryVersionSql();

    @Override
    public boolean hasSchema() {
        return true;
    }

    @Override
    public List<String> getSchemaList() throws SQLException {
        return queryForString(getQuerySchemaListSql());
    }

    /**
     * 获取查询schema的sql语句
     *
     * @return
     */
    public String getQuerySchemaListSql() {
        return null;
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String tableName) throws SQLException {
        if (StringUtils.isNotBlank(dbOptions.getSchema())) {
            return getFieldList(dbName, dbOptions.getSchema(), tableName);
        }

        return getFieldList(dbName, null, tableName);
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName, Connection connection)
        throws SQLException {
        return this.getFieldList(dbName, schema, tableName);
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName)
        throws SQLException {
        List<FieldInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection()) {

            DatabaseMetaData metaData = conn.getMetaData();
            String catalog = conn.getCatalog();

            String escape = metaData.getSearchStringEscape();

            String newTableName = tableName;
            newTableName = newTableName.replace("%", (escape + "%"));
            newTableName = newTableName.replace("_", (escape + "_"));
            if (StringUtils.isBlank(schema)) {
                schema = conn.getSchema();
            }
            if (this.dbOptions.getDatabase().equals(Database.DM)) {
                schema = dbName;
            }
            String newSchema = schema;
            if (StringUtils.isNotBlank(newSchema)) {
                newSchema = newSchema.replace("%", (escape + "%"));
                newSchema = newSchema.replace("_", (escape + "_"));
            }

            try (ResultSet rs = metaData.getColumns(catalog, newSchema, newTableName, null)) {
                while (rs != null && rs.next()) {
                    int dataType = rs.getInt("DATA_TYPE");
                    String typeName = rs.getString("TYPE_NAME");
                    String columnName = rs.getString("COLUMN_NAME");
                    int columnSize = rs.getInt("COLUMN_SIZE");
                    int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                    // 字段是否可为NULL，0-NO  1-YES
                    int nullable = rs.getInt("NULLABLE");
                    String remarks = rs.getString("REMARKS");

                    /** 拼接decimal,varchar,char */
                    if (typeName.equalsIgnoreCase("decimal")) {
                        typeName = typeName + "(" + columnSize + "," + decimalDigits + ")";
                    }
                    if (typeName.equalsIgnoreCase("varchar") || typeName.equalsIgnoreCase("char")) {
                        typeName = typeName + "(" + columnSize + ")";
                    }

                    FieldInfoDomain fieldInfo =
                        FieldInfoDomain.builder()
                            .dataType(dataType)
                            .fieldName(columnName)
                            .fieldType(typeName)
                            .columnSize(columnSize)
                            .decimalDigits(decimalDigits)
                            .isNullable(nullable == 1)
                            .comment(remarks)
                            .build();
                    list.add(fieldInfo);
                }
            }

            if (CollectionUtils.isEmpty(list)) {
                return list;
            }

            try (ResultSet rs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
                while (rs != null && rs.next()) {
                    String columnName = rs.getString("COLUMN_NAME");
                    Optional<FieldInfoDomain> optional =
                        list.stream()
                            .filter(e -> StringUtils.equals(e.getFieldName(), columnName))
                            .findFirst();
                    if (optional.isPresent()) {
                        optional.get().setPk(true);
                    }
                }
            }
        }

        return list;
    }

    @Override
    public boolean isTableNameExist(String dbName, String tableName) throws SQLException {
        final TableInfoDomain tableInfoByTableName = getTableInfoByTableName(dbName, tableName);
        return tableInfoByTableName != null;
    }

    @Override
    public boolean isTableNameExist(String dbName, String schema, String tableName)
        throws SQLException {
        final TableInfoDomain tableInfoByTableName = getTableInfoByTableName(dbName, schema, tableName);
        return tableInfoByTableName != null;
    }

    @Override
    public List<TableSizeDomain> getTableSizeList(String dbName, String schema) throws SQLException {
        return Collections.EMPTY_LIST;
    }

    @Override
    public QueryBuilder getQueryBuilder() throws SQLException {
        return new QueryBuilder(dbOptions.getDatabase(), getConnection());
    }

    @Override
    public DDLBuilder getDDLBuilder() throws SQLException {
        return new DDLBuilder(dbOptions.getDatabase(), getConnection());
    }

    @Override
    public abstract boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException;

    protected PreparedStatement createPreparedStatement(Connection conn, String sql, Object... params)
        throws SQLException {
        PreparedStatement statement = conn.prepareStatement(sql);
        if (params == null || params.length == 0) {
            return statement;
        }
        for (int index = 0; index < params.length; index++) {
            Object param = params[index];
            if (param instanceof String) {
                statement.setString(index + 1, (String) param);
            } else if (param instanceof Long) {
                statement.setLong(index + 1, (Long) param);
            } else if (param instanceof Integer) {
                statement.setInt(index + 1, (Integer) param);
            } else {
                throw new SQLException("not support param type ");
            }
        }
        return statement;
    }

    @Override
    public PreviewDataDomain previewData(String tableName) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(getPreviewDataSql(tableName));
             ResultSet rs = pstmt.executeQuery()) {
            if (rs == null) {
                return PreviewDataDomain.builder().build();
            }

            List<String> fieldNameList = new LinkedList<>();

            // 获取键名
            ResultSetMetaData meta = rs.getMetaData();

            // 获取行的数量
            int columnCount = meta.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                fieldNameList.add(meta.getColumnLabel(i));
            }

            List<List<Object>> dataList = new LinkedList<>();

            while (rs.next()) {
                List<Object> rowData = new LinkedList<>();
                for (int i = 1; i <= columnCount; i++) {
                    Object object = rs.getObject(i);
                    if (Objects.nonNull(object)) {
                        rowData.add(rs.getObject(i).toString());
                    } else {
                        rowData.add(object);
                    }

                }
                dataList.add(rowData);
            }
            return PreviewDataDomain.builder().fieldNameList(fieldNameList).dataList(dataList).build();
        }
    }

    @Override
    public PreviewDataDomain previewData(String schema, String tableName) throws SQLException {
        return previewData(tableName);
    }

    protected String getPreviewDataSql(String tableName) {
        String sql = "SELECT * from " + tableName + " LIMIT 10";
        return sql;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, List<String> viewNameLists)
        throws SQLException {
        return null;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, List<String> viewNameLists)
        throws SQLException {
        return null;
    }
}
